﻿using aspNetCURD.Database;
using aspNetCURD.Models;
using System.Collections.Generic;
using System.Linq;

namespace aspNetCURD.Services
{
    public class StudentScCourseRepository : IStudentScCourseRepository
    {
        private readonly AppDbContext _context;
        public StudentScCourseRepository(AppDbContext context)
        {
            _context = context;
        }
        public IEnumerable<StudentScCourse> GetStudentScCourses(string keyword)
        {
            if (!string.IsNullOrEmpty(keyword))
            {
                keyword = keyword.Trim();
            }
            //笔记：在使用 join on进行关联查询时，on 后面的关联条件需要先写前表的字段，再写后表的字段。
            IEnumerable<StudentScCourse> result =
                from students in _context.student
                join scs in _context.sc on students.sno equals scs.sno
                into studentScs
                from scs in studentScs.DefaultIfEmpty()
                join courses in _context.course on   scs.cno equals courses.cno
                into scCorse
                from courses in scCorse.DefaultIfEmpty()
                where students.sno.Contains(keyword) || 
                    students.sname.Contains(keyword) ||
                    scs.cno.Contains(keyword) ||
                    courses.cname.Contains(keyword) ||
                    keyword == null
                select new StudentScCourse
                {
                    sno = students.sno,
                    sname = students.sname,
                    cno = scs.cno,
                    cname = courses.cname,
                    score = scs.score
                };
            return result.ToList();
        }
    }
}
